﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace 三级联动2.Controllers
{
    public class DBHelper
    {
        // 连接对象
        private static SqlConnection cnn = null;
        public static SqlConnection Cnn
        {
            get
            {
                if (cnn == null)
                {
                    //string cnnstr = "Data Source=DELL-PC\\SQLEXPRESS;Initial Catalog=DBemp;Integrated Security=True";
                    string cnnstr = "server=.;uid=sa;pwd=li123;database=Area";
                    cnn = new SqlConnection(cnnstr);
                    cnn.Open();
                }

                return cnn;
            }
        }


        /// <summary>
        /// 返回第一行第一列的数据，用于登录，返回一个值的情况 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteScalar(string sql)
        {
            int i = 0;
            SqlCommand cmd = new SqlCommand(sql, Cnn);
            try
            {
                if (Cnn.State != ConnectionState.Open)
                {
                    Cnn.Open();
                }
                i = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
            }
            finally
            {
                Cnn.Close();
                
            }
            return i;
        }

        public static DataTable GetDataTable(string sql)
        {
            //cnn.Open();
            SqlDataAdapter sda = new SqlDataAdapter(sql, Cnn);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            sda.Dispose();
            if (cnn.State == ConnectionState.Open)
            {
                cnn.Close();
                
            }
            return dt;
        }
        public static DataTable GetDataTable(string sql, SqlParameter[] spart)
        {
            SqlDataAdapter sda = new SqlDataAdapter(sql, Cnn);
            DataTable dt = new DataTable();
            try
            {

                if (Cnn.State != ConnectionState.Open)
                {
                    Cnn.Open();
                }
                sda.Fill(dt);

            }
            catch (Exception ex)
            { }
            finally
            {
                cnn.Close();
                sda.Dispose();
            }

            return dt;
        }


        //定义聚集函数查询的方法(重载)
        public static int GetScalar(string sql)
        {
            Cnn.Close();
            Cnn.Open();
            SqlCommand cmm = new SqlCommand(sql, Cnn);
            object obj = cmm.ExecuteScalar();

            //return (int)cmm.ExecuteScalar(); 

            //------------------------------------------------------------------
            if (obj != null)
                return int.Parse(cmm.ExecuteScalar().ToString());
            else
                return 0;// -1; //为-1时SaveMySchedule.Page_Load时间出现if (scheduleId !=0)逻辑错误
        }
        public static int GetScalar(string sql, SqlParameter[] sp)
        {
            SqlCommand cmm = new SqlCommand(sql, Cnn);
            cmm.CommandType = CommandType.StoredProcedure;
            if (sp != null)
            {
                cmm.Parameters.AddRange(sp);
            }
            return int.Parse(cmm.ExecuteScalar().ToString()); //error:(int)cmm.ExecuteScalar();执行MyNoteService.AddMyNote出现错误
        }


        /// <summary>
        /// 定义操作(增删改)数据库的方法(重载)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>

        public static int ExecuteCommand(string sql)
        {
            int i = 0;
            SqlCommand cmm = new SqlCommand();
            try
            {
                if (Cnn.State != ConnectionState.Open)
                {
                    Cnn.Open();
                }
                cmm.CommandText = sql;
                cmm.Connection = Cnn;//不是cnn

                i = cmm.ExecuteNonQuery();
            }
            catch { }
            finally
            {
                cmm.Dispose();
                Cnn.Close();
            }
            return i;
        }
        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql, SqlParameter[] sp)
        {
            SqlCommand cmm = new SqlCommand();
            cmm.CommandText = sql;
            cmm.Connection = Cnn;//不是cnn+
            cmm.CommandType = CommandType.StoredProcedure;

            //cmm.Parameters.Clear();
            cmm.Parameters.AddRange(sp);

            return cmm.ExecuteNonQuery();
        }
        /// <summary>
        /// 定义得到记录集的查询方法(不含参)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>

        public static SqlDataReader GetDataReader(string sql)
        {
            SqlCommand cmm = new SqlCommand(sql, Cnn);
            return cmm.ExecuteReader();
        }
        /// <summary>
        /// 数据读取（含参数）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static SqlDataReader GetDataReader(string sql, SqlParameter[] sp)
        {
            SqlCommand cmm = new SqlCommand(sql, Cnn);
            cmm.CommandType = CommandType.StoredProcedure;
            if (sp != null)
            {
                cmm.Parameters.AddRange(sp);
            }

            return cmm.ExecuteReader();
        }




        public static int ExecuteNonQuery(string sql)
        {
            Cnn.Close();
            Cnn.Open();
            SqlCommand cmm = new SqlCommand(sql, Cnn);
            return cmm.ExecuteNonQuery();
        }

        public static int ExecuteNonQuery(string sql, SqlParameter[] spart)
        {
            SqlCommand cmm = new SqlCommand(sql, Cnn);
            cmm.CommandType = CommandType.StoredProcedure;
            if (spart != null)
            { 
                cmm.Parameters.AddRange(spart);
            }
            return cmm.ExecuteNonQuery();
        }
        public static List<T> GetList<T>(string sql)
        {
            DataTable dt = GetDataTable(sql);
            List<T> items = DataTable2List<T>(dt).ToList();
            return items;
        }
        public static List<T> GetList<T>(string sql, SqlParameter[] spart)
        {
            DataTable dt = GetDataTable(sql, spart);
            List<T> items = DataTable2List<T>(dt).ToList();
            return items;
        }

        /// <summary>
        /// DataSet装换为泛型集合     
        /// </summary>     
        /// <typeparam name="T"></typeparam>    
        /// <param name="p_DataSet">DataSet</param>    
        /// <param name="p_TableIndex">待转换数据表索引</param>    
        /// <returns></returns>  

        private static IList<T> DataTable2List<T>(DataTable p_Data)
        {
            // 反射技术
            // 返回值初始化       
            IList<T> result = new List<T>();
            for (int j = 0; j < p_Data.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < p_Data.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值       
                        if (pi.Name.Equals(p_Data.Columns[i].ColumnName))
                        {
                            // 数据库NULL值单独处理       
                            if (p_Data.Rows[j][i] != DBNull.Value)
                                pi.SetValue(_t, p_Data.Rows[j][i], null);
                            else
                                pi.SetValue(_t, null, null);
                            break;
                        }
                    }
                }
                result.Add(_t);
            }
            return result;
        }
    }
}
